package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectType;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;

public final class GraduateProjectTypeDao {
    private static GraduateProjectTypeDao graduateProjectTypeDao;

    private GraduateProjectTypeDao() {
    }

    public static GraduateProjectTypeDao getInstance() {
        if (graduateProjectTypeDao == null) {
            graduateProjectTypeDao = new GraduateProjectTypeDao();
        }
        return graduateProjectTypeDao;
    }

    public static Collection<GraduateProjectType> findAll(Connection connection) throws SQLException {
        Collection<GraduateProjectType> graduateProjectTypes = new HashSet<GraduateProjectType>();
        int totalNum = CommonDao.count(connection, "GraduateProjectType");
        StringBuilder sql_select = new StringBuilder("SELECT*FROM GraduateProjectType");

        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()) {

            //根据id查找结果创建GraduateProjectType对象
            GraduateProjectType graduateProjectType = new GraduateProjectType(
                    resultSet_select.getInt("id"),
                    resultSet_select.getString("description"),
                    resultSet_select.getString("no"),
                    resultSet_select.getString("remarks"));
            graduateProjectTypes.add(graduateProjectType);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return graduateProjectTypes;
    }


    //根据id，查找学院
    public GraduateProjectType find(Integer id, Connection connection) throws SQLException {

        String findGraduateProjectType_sql = "select * from GraduateProjectType where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(findGraduateProjectType_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找学院
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建GraduateProjectType对象
        GraduateProjectType graduateProjectType = new GraduateProjectType(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭
        JdbcHelper.close(pstmt, connection);
        return graduateProjectType;
    }

    //修改
    public boolean update(GraduateProjectType graduateProjectType, Connection connection) throws SQLException {

        String updateGraduateProjectType_sql = "" +
                "UPDATE GraduateProjectType SET no = ?,description = ?,remarks = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGraduateProjectType_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProjectType.getNo());
        pstmt.setString(2, graduateProjectType.getDescription());
        pstmt.setString(3, graduateProjectType.getRemarks());
        pstmt.setInt(4, graduateProjectType.getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }

    //添加学院
    public boolean add(GraduateProjectType graduateProjectType, Connection connection) throws SQLException {

        String addGraduateProjectType_sql = "" +
                "INSERT INTO GraduateProjectType(no,description,remarks) VALUES" + "(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGraduateProjectType_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProjectType.getNo());
        pstmt.setString(2, graduateProjectType.getDescription());
        pstmt.setString(3, graduateProjectType.getRemarks());
        //添加学院 并判断是否添加成功
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }

    //根据GraduateProjectType对象删除
    public static boolean delete(GraduateProjectType graduateProjectType, Connection connection) throws SQLException {

        String deleteGraduateProjectType_sql = "delete from GraduateProjectType where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGraduateProjectType_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProjectType.getNo());
        //根据id删除学院 并判断是否删除成功
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        return  affectedRowNum> 0;
    }
}
